package com.checknull.druineage.analysis;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.util.StringUtils;
import com.checknull.druineage.customizable.ColumnFilter;
import com.checknull.druineage.customizable.UsedForCal;
import com.checknull.druineage.element.Column;
import com.checknull.druineage.element.DB;
import com.checknull.druineage.element.Schema;
import com.checknull.druineage.element.Table;
import com.checknull.druineage.exception.AsteriskException;
import com.checknull.druineage.util.ClassUtils;
import com.checknull.druineage.util.Md5Utils;

import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

import static com.checknull.druineage.constant.Const.*;

public class Lineage {

    /**
     * 数据库类型
     */
    private String dbType;

    /**
     * 脚本中''的临时替换
     *
     * @description oracle脚本中的''空默认会转换成null，实际上不该完全等价，比如replace函数中的参数为空
     */
    private String blankTempReplacement;

    /**
     * 为解析出的未知字段设置前缀
     */
    private String unknownColumnPrefix = UNKNOWN + "_";

    private ColumnFilter columnFilter = new ColumnFilter(new UsedForCal());

    public Lineage() {
    }

    public Lineage(String dbType) {
        this.dbType = dbType;
    }

    public Lineage setDbType(String dbType) {
        this.dbType = dbType;
        return this;
    }

    public Lineage setBlankTempReplacement(String blankTempReplacement) {
        this.blankTempReplacement = StringUtils.isEmpty(blankTempReplacement) ? null : blankTempReplacement.toUpperCase();
        return this;
    }

    public Lineage setUnknownColumnPrefix(String unknownColumnPrefix) {
        this.unknownColumnPrefix = StringUtils.isEmpty(unknownColumnPrefix) ? null : unknownColumnPrefix.toUpperCase();
        return this;
    }

    public Lineage setColumnFilter(ColumnFilter columnFilter) {
        this.columnFilter = columnFilter;
        return this;
    }

    /**
     * sql过滤后开始解析
     *
     * @param stmtList
     * @return
     */
    public DB analysis(List<SQLStatement> stmtList) {

        //过滤非血缘sql
        stmtList.removeIf(stmt -> !(stmt instanceof SQLDropTableStatement)
                && !(stmt instanceof SQLCreateTableStatement)
                && !(stmt instanceof SQLInsertStatement));

        DB db = new DB();
        for (SQLStatement statement : stmtList) {
            if (statement instanceof SQLDropTableStatement) {    //删表语句
                SQLDropTableStatement stmt = (SQLDropTableStatement) statement;
                for (SQLExprTableSource tableSource : stmt.getTableSources()) {

                    Schema schema = new Schema();

                    //获取schema
                    if (tableSource.getExpr() instanceof SQLPropertyExpr) {
                        schema.setName(((SQLIdentifierExpr) ((SQLPropertyExpr) tableSource.getExpr()).getOwner()).getName());
                    }
                    //与现有schema比对合并最终schema
                    schema = db.absorbSchema(schema);

                    Table table = new Table();
                    //获取表
                    if (tableSource.getExpr() instanceof SQLPropertyExpr) {
                        table.setName(((SQLPropertyExpr) tableSource.getExpr()).getName());
                    } else {
                        table.setName(((SQLIdentifierExpr) tableSource.getExpr()).getName());
                    }
                    //与现有表比对冰合成最终表
                    table = schema.absorbTable(table);
                    //标记删除表
                    table.isDrop(true);

                }
            } else if (statement instanceof SQLCreateTableStatement) {   //建表语句
                SQLCreateTableStatement stmt = (SQLCreateTableStatement) statement;

                Schema schema = new Schema();
                //获取schema
                if (stmt.getTableSource().getExpr() instanceof SQLPropertyExpr) {
                    schema.setName(((SQLIdentifierExpr) ((SQLPropertyExpr) stmt.getTableSource().getExpr()).getOwner()).getName());
                }
                //与现有schema比对合并最终schema
                schema = db.absorbSchema(schema);

                Table table = new Table();
                //获取表
                if (stmt.getTableSource().getExpr() instanceof SQLPropertyExpr) {
                    table.setName(((SQLPropertyExpr) stmt.getTableSource().getExpr()).getName());
                } else {
                    table.setName(((SQLIdentifierExpr) stmt.getTableSource().getExpr()).getName());
                }
                //与现有表比对冰合成最终表
                table = schema.absorbTable(table);
                if (!stmt.getTableElementList().isEmpty()) {
                    for (SQLTableElement tableElement : stmt.getTableElementList()) {
                        if (tableElement instanceof SQLColumnDefinition) {
                            Column column = new Column();
                            column.setName(((SQLColumnDefinition) tableElement).getName().toString());
                            table.absorbColumn(column);
                        }
                    }
                }
                if (stmt.getSelect() != null) {
                    selectForCreateAnalysis(db, stmt.getSelect().getQuery(), table, !stmt.getTableElementList().isEmpty());
                }

                //去除血缘中的临时表
                table.clearTempTable(columnFilter.getUsedForCal());

            } else if (statement instanceof SQLInsertStatement) {    //插入语句
                SQLInsertStatement stmt = (SQLInsertStatement) statement;

                //过滤纯insert无血缘sql
                if (stmt.getQuery() == null) {
                    continue;
                }

                Schema schema = new Schema();
                //获取schema
                if (stmt.getTableSource().getExpr() instanceof SQLPropertyExpr) {
                    schema.setName(((SQLIdentifierExpr) ((SQLPropertyExpr) stmt.getTableSource().getExpr()).getOwner()).getName());
                }
                //与现有schema比对合并最终schema
                schema = db.absorbSchema(schema);

                Table table = new Table();
                //获取表
                if (stmt.getTableSource().getExpr() instanceof SQLPropertyExpr) {
                    table.setName(((SQLPropertyExpr) stmt.getTableSource().getExpr()).getName());
                } else {
                    table.setName(((SQLIdentifierExpr) stmt.getTableSource().getExpr()).getName());
                }
                //与现有表比对冰合成最终表
                table = schema.absorbTable(table);

                if (stmt.getColumns().isEmpty()) {  //inser部分未设置字段
                    selectForCreateAnalysis(db, stmt.getQuery().getQuery(), table);
                } else {    //insert部分已设置字段
                    for (int i = 0; i < stmt.getColumns().size(); i++) {
                        SQLExpr expr = stmt.getColumns().get(i);
                        Column column = new Column();
                        column.setName(((SQLIdentifierExpr) expr).getName());
                        table.absorbColumn(column, i);
                    }
                    selectForCreateAnalysis(db, stmt.getQuery().getQuery(), table, true);
                }

                //去除血缘中的临时表
                table.clearTempTable(columnFilter.getUsedForCal());

            }
        }

        //常量不能归属于任何表，所以删除所有表中的常量字段
        db.clearValColumn();

        return db;
    }

    /**
     * 先格式化sql，在解析
     *
     * @param sql
     * @return
     */
    public DB analysis(String sql) {
        sql = sql.toUpperCase();
        if (!StringUtils.isEmpty(blankTempReplacement)) {
            sql = sql.replace(DB_BLANK, "'" + blankTempReplacement + "'");
        }
        sql = SQLUtils.format(sql, dbType);
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
        return analysis(stmtList);
    }

    public DB analysis(String[] sqls) {
        return analysis(Arrays.asList(sqls));
    }

    /**
     * 过滤无用sql
     *
     * @param sqls
     * @return
     */
    public DB analysis(Collection<String> sqls) {
        List<SQLStatement> stmtList = new ArrayList<>();
        for (String sql : sqls) {
            sql = sql.toUpperCase();
            if (!StringUtils.isEmpty(blankTempReplacement)) {
                sql = sql.replace(DB_BLANK, "'" + blankTempReplacement + "'");
            }
            try {
                sql = SQLUtils.format(sql, dbType);
                stmtList.addAll(SQLUtils.parseStatements(sql, dbType));
            } catch (ParserException ignored) {
            }
        }
        return analysis(stmtList);
    }

    /**
     * 解析insert into select的select部分
     *
     * @param db
     * @param selectQuery
     * @param tableAlias
     * @param targetColAlreadySure
     */
    private void selectForCreateAnalysis(DB db, SQLSelectQuery selectQuery, Table tableAlias, boolean targetColAlreadySure) {
        if (selectQuery instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) selectQuery;
            List<SQLSelectItem> selectItems = queryBlock.getSelectList();
            int unknownColumnIndex = 0;

            //建立SQLSelectItem与Column关系，便于准确拿取
            Map<SQLSelectItem, Column> selectItemColumnMap = new HashMap<>();

            //存放常量
            Table constTable = new Table();
            constTable.isTemp(true);
            constTable.setName(CONSTANT);

            //为目标字段一个一个地分批找源字段，互补干扰
            for (int i = 0; i < selectItems.size(); i++) {
                SQLSelectItem selectItem = selectItems.get(i);
                Column column = new Column();
                if (targetColAlreadySure) {
                    //insert into部分已经设置字段
                    column = tableAlias.getColumns().get(i);
                } else {
                    if (StringUtils.isEmpty(selectItem.getAlias())) {
                        if (selectItem.getExpr() instanceof SQLIdentifierExpr) {
                            column.setName(((SQLIdentifierExpr) selectItem.getExpr()).getName());
                        } else if (selectItem.getExpr() instanceof SQLPropertyExpr) {
                            column.setName(((SQLPropertyExpr) selectItem.getExpr()).getName());
                        } else {    //设置未知字段
                            column.setName(unknownColumnPrefix + unknownColumnIndex);

                            //常量非空字段拼上值
                            if (selectItem.getExpr() instanceof SQLValuableExpr || !selectItem.getExpr().getClass().equals(SQLNullExpr.class)) {
                                String value = ((SQLValuableExpr) selectItem.getExpr()).getValue().toString();
                                if (!value.equals(blankTempReplacement)) {
                                    column.setName(column.getName() + "(" + value + ")");
                                }
                            }

                            unknownColumnIndex++;
                        }
                        column.isUnknown(column.getName().contains(unknownColumnPrefix));
                    } else {
                        //selec部分有别名的，将目标表的字段名称设置为别名
                        column.setName(selectItem.getAlias());
                    }
                    //设置目标字段
                    column = tableAlias.absorbColumn(column);
                }
                selectItemColumnMap.put(selectItem, column);
                Schema tempSchema = new Schema();
                //查找源字段
                findColumn(selectItem, tempSchema, queryBlock, selectItemColumnMap.get(selectItem));
                for (Table tempTable : tempSchema.getTables()) {
                    tempTable.isTemp(true);
                    for (Column tempColumn : tempTable.getColumns()) {
                        //常量在血缘中肯定会有多次作用，肯定要合并
                        if (CONSTANT.equals(tempTable.getName())) {
                            constTable.absorbColumn(tempColumn);
                            for (Column constColumn : constTable.getColumns()) {
                                if (tempTable.getName().equals(constColumn.getName()) && !tempColumn.getUsedFor().equals(constColumn.getUsedFor())) {
                                    constColumn.getUsedFor().putAll(tempColumn.getUsedFor());
                                    tempColumn.getUsedFor().putAll(constColumn.getUsedFor());
                                }
                            }
                        }
                        //把源字段设置到目标字段上
                        column.absorbSrcColumn(tempColumn);
                    }
                }
            }

            Schema tempSchema = new Schema();
            //常量要重复利用
            tempSchema.absorbTable(constTable);

            //在同一批次查找源字段
            for (SQLSelectItem selectItem : selectItems) {
                findColumn(selectItem, tempSchema, queryBlock, selectItemColumnMap.get(selectItem));
            }

            //查找原表
            findTable(db, queryBlock.getFrom(), tempSchema, queryBlock);

            //将目标表与原表合并
            mergeLineage(tableAlias, tempSchema.getTables(), queryBlock);
        } else if (selectQuery instanceof SQLUnionQuery) {
            AtomicReference<Boolean> ifTargetColAlreadySure = new AtomicReference<>(targetColAlreadySure);
            ClassUtils.loopField(SQLUnionQuery.class, selectQuery, o -> {
                if (o instanceof SQLSelectQueryBlock) {
                    selectForCreateAnalysis(db, (SQLSelectQueryBlock) o, tableAlias, ifTargetColAlreadySure.get());
                    ifTargetColAlreadySure.set(true);
                } else if (o instanceof SQLUnionQuery) {
                    unionSelectForCreateAnalysis(db, (SQLUnionQuery) o, tableAlias);
                }
            });
        }
    }

    private void selectForCreateAnalysis(DB db, SQLSelectQuery selectQuery, Table tableAlias) {
        selectForCreateAnalysis(db, selectQuery, tableAlias, false);
    }

    /**
     * 解析insert into select后的union部分
     *
     * @param db
     * @param selectQuery
     * @param tableAlias
     */
    private void unionSelectForCreateAnalysis(DB db, SQLSelectQuery selectQuery, Table tableAlias) {
        if (selectQuery instanceof SQLSelectQueryBlock) {
            selectForCreateAnalysis(db, selectQuery, tableAlias, true);
        } else if (selectQuery instanceof SQLUnionQuery) {
            ClassUtils.loopField(selectQuery.getClass(), selectQuery, o -> {
                if (o instanceof SQLSelectQuery) {
                    unionSelectForCreateAnalysis(db, (SQLSelectQuery) o, tableAlias);
                }
            });
        }
    }

    /**
     * 解析普通select部分
     *
     * @param db
     * @param queryBlock
     * @param tableAlias
     * @param nextUnionSortedTargetCols
     */
    private void selectForSelectAnalysis(DB db, SQLSelectQueryBlock queryBlock, Table tableAlias, ArrayList<Column> nextUnionSortedTargetCols) {
        if (nextUnionSortedTargetCols == null) {
            nextUnionSortedTargetCols = new ArrayList<>();
        }
        boolean nextUnion = !nextUnionSortedTargetCols.isEmpty();

        try {
            //替换
            replaceAsteriskFromOuter(queryBlock, tableAlias, nextUnion);
        } catch (RuntimeException e) {
            e.printStackTrace();
        }

        List<SQLSelectItem> selectItems = queryBlock.getSelectList();
        int unknownColumnIndex = 0;

        //建立SQLSelectItem与Column关系，便于准确拿取
        Map<SQLSelectItem, Column> selectItemColumnMap = new HashMap<>();

        //存放常量
        Table constTable = new Table();
        constTable.isTemp(true);
        constTable.setName(CONSTANT);

        //为目标字段一个一个地分批找源字段，互不干扰
        for (int i = 0; i < selectItems.size(); i++) {
            SQLSelectItem selectItem = selectItems.get(i);
            Column column = new Column();
            if (nextUnion) {
                //union后面的select中识别出的字段名称无意义
                column = nextUnionSortedTargetCols.get(i);
            } else {
                if (StringUtils.isEmpty(selectItem.getAlias())) {
                    if (selectItem.getExpr() instanceof SQLIdentifierExpr) {
                        column.setName(((SQLIdentifierExpr) selectItem.getExpr()).getName());
                    } else if (selectItem.getExpr() instanceof SQLPropertyExpr) {
                        column.setName(((SQLPropertyExpr) selectItem.getExpr()).getName());
                    } else {    //设置未知字段
                        column.setName(unknownColumnPrefix + unknownColumnIndex);

                        //常量非空字段拼上值
                        if (selectItem.getExpr() instanceof SQLValuableExpr || !selectItem.getExpr().getClass().equals(SQLNullExpr.class)) {
                            String value = ((SQLValuableExpr) selectItem.getExpr()).getValue().toString();
                            if (!value.equals(blankTempReplacement)) {
                                column.setName(column.getName() + "(" + value + ")");
                            }
                        }

                        unknownColumnIndex++;
                    }
                    column.isUnknown(column.getName().contains(unknownColumnPrefix));
                } else {
                    //select部分有别名的，将临时目标表的字段名称设置为别名
                    column.setName(selectItem.getAlias());
                }
                //为临时目标表设置字段
                column = tableAlias.absorbColumn(column);

                //为下一次union后面的select存放好临时目标表字段
                nextUnionSortedTargetCols.add(column);
            }
            selectItemColumnMap.put(selectItem, column);
            Schema tempSchema = new Schema();
            //查找源字段
            findColumn(selectItem, tempSchema, queryBlock, selectItemColumnMap.get(selectItem));
            for (Table tempTable : tempSchema.getTables()) {
                tempTable.isTemp(true);
                for (Column tempColumn : tempTable.getColumns()) {
                    //常量在血缘中肯定会有多次作用，肯定要合并
                    if (CONSTANT.equals(tempTable.getName())) {
                        constTable.absorbColumn(tempColumn);
                        for (Column constColumn : constTable.getColumns()) {
                            if (tempTable.getName().equals(constColumn.getName()) && !tempColumn.getUsedFor().equals(constColumn.getUsedFor())) {
                                constColumn.getUsedFor().putAll(tempColumn.getUsedFor());
                                tempColumn.getUsedFor().putAll(constColumn.getUsedFor());
                            }
                        }
                    }
                    //把源字段设置到目标字段上
                    column.absorbSrcColumn(tempColumn);
                }
            }
        }

        Schema tempSchema = new Schema();
        //常量要重复利用
        tempSchema.absorbTable(constTable);

        //在同一批次查找源字段
        for (SQLSelectItem selectItem : selectItems) {
            findColumn(selectItem, tempSchema, queryBlock, selectItemColumnMap.get(selectItem));
        }

        //查找原表
        findTable(db, queryBlock.getFrom(), tempSchema, queryBlock);

        //将目标表与原表合并
        mergeLineage(tableAlias, tempSchema.getTables(), queryBlock);
    }

    private void selectForSelectAnalysis(DB db, SQLSelectQueryBlock queryBlock, Table tableAlias) {
        selectForSelectAnalysis(db, queryBlock, tableAlias, null);
    }

    /**
     * 解析普通union部分
     *
     * @param db
     * @param selectQuery
     * @param tableAlias
     * @param nextUnionSortedTargetCols
     */
    private void unionSelectForSelectAnalysis(DB db, SQLSelectQuery selectQuery, Table tableAlias, ArrayList<Column> nextUnionSortedTargetCols) {
        if (selectQuery instanceof SQLSelectQueryBlock) {
            selectForSelectAnalysis(db, (SQLSelectQueryBlock) selectQuery, tableAlias, nextUnionSortedTargetCols);
        } else if (selectQuery instanceof SQLUnionQuery) {
            ClassUtils.loopField(selectQuery.getClass(), selectQuery, o -> {
                if (o instanceof SQLSelectQuery) {
                    unionSelectForSelectAnalysis(db, (SQLSelectQuery) o, tableAlias, nextUnionSortedTargetCols);
                }
            });
        }
    }

    /**
     * 解析完成后将目标表与源表结合
     *
     * @param targetTable
     * @param srcTables
     * @param queryBlock
     */
    private void mergeLineage(Table targetTable, List<Table> srcTables, SQLSelectQueryBlock queryBlock) {
        List<Column> columns = targetTable.getColumns();
        for (Table srcTable : srcTables) {
            for (Column srcColumn : srcTable.getColumns()) {
                for (Column column : columns) {
                    List<Column> tempSrcColumns = column.getSrcColumns();
                    //遍历临时源字段
                    for (int i = 0; i < tempSrcColumns.size(); i++) {
                        Column tempSrcColumn = tempSrcColumns.get(i);
                        if (srcColumn.getName().equals(tempSrcColumn.getName())
                                && ((srcColumn.getOwner() == null && tempSrcColumn.getOwner() == null)
                                || (srcColumn.getOwner() != null && tempSrcColumn.getOwner() != null
                                && srcColumn.getOwner().getAlias(queryBlock).contains(tempSrcColumn.getOwner().getName())))
                                && srcColumn.getUsedFor(column) != null) {
                            //将临时源字段替换成真实源字段
                            tempSrcColumns.set(i, srcColumn);
                            break;
                        }
                    }
                    //去重
                    column.setSrcColumns(tempSrcColumns.stream().distinct().collect(Collectors.toList()));
                }
            }
        }
    }

    /**
     * 替换*（根据外面信息）
     *
     * @param queryBlock  select整体部分
     * @param tableAlias  临时目标表
     * @param isNextUnion 是否是union后面部分的select
     */
    private void replaceAsteriskFromOuter(SQLSelectQueryBlock queryBlock, Table tableAlias, boolean isNextUnion) {

        //去掉常量字段的干扰
        List<Column> outerCols = tableAlias.getColumns().stream().filter(col -> !col.isVal()).collect(Collectors.toList());

        if (outerCols.isEmpty() || outerCols.stream().anyMatch(column -> "*".equals(column.getName()))) {
            //查询外部没有字段，或者包含*，则内部*替换不了
            return;
        }

        List<SQLSelectItem> selectItems = queryBlock.getSelectList();

        //若select部分存在*
        if (selectItems.stream().anyMatch(selectItem -> selectItem.getExpr() instanceof SQLAllColumnExpr || (selectItem.getExpr() instanceof SQLPropertyExpr && "*".equals(((SQLPropertyExpr) selectItem.getExpr()).getName())))) {
            if (queryBlock.getFrom() instanceof SQLExprTableSource) {   //若from部分是简单表
                if (isNextUnion) {  //若是union后面的select，则*根本识别不出
                    selectItems.removeIf(selectItem -> selectItem.getExpr() instanceof SQLAllColumnExpr || (selectItem.getExpr() instanceof SQLPropertyExpr && "*".equals(((SQLPropertyExpr) selectItem.getExpr()).getName())));
                    //将*替换成未知字段（未知字段数量+已知字段数量=外部临时表非常量字段数量）
                    int loopCount = outerCols.size() - selectItems.size();
                    for (int i = 0; i < loopCount; i++) {
                        queryBlock.addSelectItem(new SQLIdentifierExpr(unknownColumnPrefix + i));
                    }
                } else {
                    //若不是union后面的select，则外部临时表的字段名肯定全部出现在select的*中，将*替换成外部临时表的非常量字段名
                    selectItems.clear();
                    for (Column column : outerCols) {
                        queryBlock.addSelectItem(new SQLIdentifierExpr(column.getName()));
                    }
                }
            } else if (!StringUtils.isEmpty(queryBlock.getFrom().getAlias())) { //from后面非简单表，但有别名
                selectItems.removeIf(selectItem -> selectItem.getExpr() instanceof SQLAllColumnExpr || (selectItem.getExpr() instanceof SQLPropertyExpr && "*".equals(((SQLPropertyExpr) selectItem.getExpr()).getName())));
                if (isNextUnion) {  //若是union后面的select，则*根本识别不出
                    //将*替换成未知字段（未知字段数量+已知字段数量=外部临时表非常量字段数量）
                    int loopCount = outerCols.size() - selectItems.size();
                    for (int i = 0; i < loopCount; i++) {
                        queryBlock.addSelectItem(new SQLPropertyExpr(queryBlock.getFrom().getAlias(), unknownColumnPrefix + i));
                    }
                } else {
                    //若不是union后面的select，则外部临时表的字段名肯定全部出现在select的*中，将*替换成外部临时表的非常量字段名
                    for (Column column : outerCols) {
                        if (selectItems.stream().noneMatch(selectItem -> {
                            if (column.getName().equalsIgnoreCase(selectItem.getAlias())) {
                                return true;
                            } else if (StringUtils.isEmpty(selectItem.getAlias())) {
                                SQLExpr selectItemExpr = selectItem.getExpr();
                                return (selectItemExpr instanceof SQLIdentifierExpr && column.getName().equalsIgnoreCase(((SQLIdentifierExpr) selectItemExpr).getName()))
                                        || (selectItemExpr instanceof SQLPropertyExpr && column.getName().equalsIgnoreCase(((SQLPropertyExpr) selectItemExpr).getName()));
                            }
                            return false;
                        })) {
                            queryBlock.addSelectItem(new SQLPropertyExpr(queryBlock.getFrom().getAlias(), column.getName()));
                        }
                    }
                }
            } else if (queryBlock.getFrom() instanceof SQLJoinTableSource) {    //若from部分为多表关联
                String sql = queryBlock.getParent().toString();

                //若select部分有未加别名的*，则无法解析
                if (selectItems.stream().anyMatch(selectItem -> selectItem.getExpr() instanceof SQLAllColumnExpr)) {
                    throw new AsteriskException("the * in the sql is not clear at all", sql);
                }

                //若select部分加别名的*多于1个，则无法解析
                AtomicReference<String> asteriskOwnerName = new AtomicReference<>();
                if (selectItems.stream().filter(selectItem -> {
                    if (selectItem.getExpr() instanceof SQLPropertyExpr && "*".equals(((SQLPropertyExpr) selectItem.getExpr()).getName())) {
                        asteriskOwnerName.set(((SQLPropertyExpr) selectItem.getExpr()).getOwnernName());
                        return true;
                    }
                    return false;
                }).count() > 1) {
                    throw new AsteriskException("the * in the sql is not clear at all", sql);
                }

                selectItems.removeIf(selectItem -> selectItem.getExpr() instanceof SQLAllColumnExpr || (selectItem.getExpr() instanceof SQLPropertyExpr && "*".equals(((SQLPropertyExpr) selectItem.getExpr()).getName())));
                if (isNextUnion) {  //若是union后面的select，则*根本识别不出
                    //将*替换成未知字段（未知字段数量+已知字段数量=外部临时表非常量字段数量）
                    int loopCount = outerCols.size() - selectItems.size();
                    for (int i = 0; i < loopCount; i++) {
                        queryBlock.addSelectItem(new SQLPropertyExpr(asteriskOwnerName.get(), unknownColumnPrefix + i));
                    }
                } else {
                    //若不是union后面的select，则外部临时表的字段名肯定全部出现在select的*中，将*替换成外部临时表的非常量字段名（排除已知字段）
                    for (Column column : outerCols) {
                        if (selectItems.stream().noneMatch(selectItem -> {
                            if (column.getName().equalsIgnoreCase(selectItem.getAlias())) {
                                return true;
                            } else if (StringUtils.isEmpty(selectItem.getAlias())) {
                                SQLExpr selectItemExpr = selectItem.getExpr();
                                return (selectItemExpr instanceof SQLIdentifierExpr && column.getName().equalsIgnoreCase(((SQLIdentifierExpr) selectItemExpr).getName()))
                                        || (selectItemExpr instanceof SQLPropertyExpr && column.getName().equalsIgnoreCase(((SQLPropertyExpr) selectItemExpr).getName()));
                            }
                            return false;
                        })) {
                            queryBlock.addSelectItem(new SQLPropertyExpr(asteriskOwnerName.get(), column.getName()));
                        }
                    }
                }
            }
        }
    }

    /**
     * 查找字段
     *
     * @param columnObj 可能的当前字段对象
     * @param result    临时存放（schema或table）
     * @param scope     所处整个select部分
     * @param targetCol 目标字段
     * @param usedFor   血缘作用
     */
    private void findColumn(SQLObject columnObj, Object result, SQLSelectQueryBlock scope, Column targetCol, Set<String> usedFor) {
        if (columnObj instanceof SQLExpr || columnObj instanceof SQLSelectItem || columnObj instanceof SQLCaseExpr.Item || columnObj instanceof SQLCaseStatement.Item) {
            if (result.getClass().equals(Table.class)) {
                Table table = (Table) result;

                Column column = new Column();
                if (columnObj.getClass().equals(SQLPropertyExpr.class)) {
                    column.setName(((SQLPropertyExpr) columnObj).getName());
                    //与现有字段对比并且合成最终字段
                    column = table.absorbColumn(column);
                    column.isUnknown(column.getName().contains(unknownColumnPrefix));
                    column.putUsedFor(targetCol, usedFor);
                } else if (columnObj.getClass().equals(SQLIdentifierExpr.class)) {
                    column.setName(((SQLIdentifierExpr) columnObj).getName());
                    //与现有字段对比并且合成最终字段
                    column = table.absorbColumn(column);
                    column.isUnknown(column.getName().contains(unknownColumnPrefix));
                    column.putUsedFor(targetCol, usedFor);
                } else if (columnObj instanceof SQLValuableExpr) {
                    //设置字段为常量
                    column.isVal(true);
                    if (columnObj.getClass().equals(SQLNullExpr.class)) {
                        //设置字段名为null
                        column.setName(NULL);
                    } else {
                        column.setName(((SQLValuableExpr) columnObj).getValue().toString());
                        //恢复空字符
                        if (column.getName().equalsIgnoreCase(blankTempReplacement)) {
                            column.setName("");
                        }
                    }
                    //与现有字段对比并且合成最终字段
                    column = table.absorbColumn(column);
                    //让字段记录目标字段和对应的血缘作用
                    column.putUsedFor(targetCol, usedFor);
                } else if (columnObj instanceof SQLCaseExpr) {
                    SQLCaseExpr caseExpr = (SQLCaseExpr) columnObj;
                    //处理case when中的每个when then
                    columnFilter.caseFilter(caseExpr, usedFor, (sqlObj, uf) -> findColumn(sqlObj, table, scope, targetCol, uf));
                } else if (columnObj instanceof SQLCaseExpr.Item) {
                    SQLCaseExpr.Item caseExprItem = (SQLCaseExpr.Item) columnObj;
                    //分别处理when和then
                    columnFilter.caseItemFilter(caseExprItem, usedFor, (sqlObj, uf) -> findColumn(sqlObj, table, scope, targetCol, uf));
                } else if (columnObj instanceof SQLMethodInvokeExpr) {
                    SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) columnObj;
                    //处理普通函数
                    columnFilter.methodFilter(methodInvokeExpr, usedFor, (sqlObj, uf) -> findColumn(sqlObj, table, scope, targetCol, uf));
                } else {
                    //寻找内部字段
                    ClassUtils.loopField(columnObj.getClass(), columnObj, o -> {
                        if (o instanceof List) {
                            for (Object x : (List) o) {
                                if (x instanceof SQLObject) {
                                    findColumn((SQLObject) x, table, scope, targetCol);
                                }
                            }
                        } else if (o instanceof SQLObject) {
                            findColumn((SQLObject) o, table, scope, targetCol);
                        }
                    });
                }
            } else if (result.getClass().equals(Schema.class)) {
                Schema schema = (Schema) result;
                if (columnObj.getClass().equals(SQLPropertyExpr.class)) {
                    Table table = new Table();
                    table.isTemp(true);
                    SQLIdentifierExpr tableExpr = (SQLIdentifierExpr) ((SQLPropertyExpr) columnObj).getOwner();
                    //临时表的名称设置为字段的别名
                    table.setName(tableExpr.getName());
                    //与现有表比对并合成最终表
                    table = schema.absorbTable(table);
                    //让表记录作用域和对应的表别名
                    table.putAlias(scope, table.getName());
                    Column column = new Column();
                    column.setName(((SQLPropertyExpr) columnObj).getName());
                    column.isUnknown(column.getName().contains(unknownColumnPrefix));
                    //与现有字段比对并合成最终字段
                    column = table.absorbColumn(column);
                    //让字段记录目标字段和对应的血缘作用
                    column.putUsedFor(targetCol, usedFor);
                } else if (columnObj instanceof SQLValuableExpr) {
                    Table table = new Table();
                    table.isTemp(true);
                    //临时表是常量表
                    table.setName(CONSTANT);
                    //与现有表比对并合成最终表
                    table = schema.absorbTable(table);
                    //让表记录作用域和对应的表别名
                    table.putAlias(scope, table.getName());
                    Column column = new Column();
                    //设置字段为常量
                    column.isVal(true);
                    if (columnObj.getClass().equals(SQLNullExpr.class)) {
                        //设置字段未null
                        column.setName(NULL);
                    } else {
                        column.setName(((SQLValuableExpr) columnObj).getValue().toString());
                        //恢复空字符
                        if (column.getName().equalsIgnoreCase(blankTempReplacement)) {
                            column.setName("");
                        }
                    }
                    //与现有字段对比并且合成最终字段
                    column = table.absorbColumn(column);
                    //让字段记录目标字段和对应的血缘作用
                    column.putUsedFor(targetCol, usedFor);
                } else {

                    //处理简单select from语句（支持不用别名）
                    if (columnObj instanceof SQLSelectItem) {
                        SQLSelectItem selectItem = (SQLSelectItem) columnObj;
                        if (selectItem.getParent() instanceof SQLSelectQueryBlock) {
                            SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) selectItem.getParent();
                            if (queryBlock.getFrom() instanceof SQLExprTableSource) {   //from部分是简单表
                                SQLExprTableSource tableSource = (SQLExprTableSource) queryBlock.getFrom();
                                Table table = new Table();
                                //非临时表
                                table.isTemp(false);
                                SQLExpr tableSourceExpr = tableSource.getExpr();
                                //设置真表名
                                if (tableSourceExpr instanceof SQLPropertyExpr) {
                                    table.setName(((SQLPropertyExpr) tableSourceExpr).getName());
                                } else if (tableSourceExpr instanceof SQLIdentifierExpr) {
                                    table.setName(((SQLIdentifierExpr) tableSourceExpr).getName());
                                }
                                //与现有表比对并合成最终表
                                table = schema.absorbTable(table);
                                //让表记录作用域和对应的表别名
                                table.putAlias(queryBlock, table.getName());
                                //继续查找字段
                                findColumn(selectItem, table, queryBlock, targetCol);
                                return;
                            } else if (!StringUtils.isEmpty(queryBlock.getFrom().getAlias())) {
                                Table table = new Table();
                                table.isTemp(true);
                                //临时表的名称设置为from的别名
                                table.setName(queryBlock.getFrom().getAlias());
                                //与现有表比对并合成最终表
                                table = schema.absorbTable(table);
                                //让表记录作用域和对应的表别名
                                table.putAlias(queryBlock, table.getName());
                                //继续查找字段
                                findColumn(selectItem, table, queryBlock, targetCol);
                                return;
                            } else if (queryBlock.getFrom() instanceof SQLSubqueryTableSource
                                    || queryBlock.getFrom() instanceof SQLUnionQueryTableSource) {  //from部分是子查询
                                SQLTableSource tableSource = queryBlock.getFrom();
                                Table table = new Table();
                                table.isTemp(true);
                                //将整个子查询部分md5加密作为临时表
                                table.setName(Md5Utils.encrypt(tableSource.toString().getBytes()));
                                //与现有表比对并合成最终表
                                table = schema.absorbTable(table);
                                //让表记录作用域和对应的表别名
                                table.putAlias(queryBlock, table.getName());
                                //继续查找字段
                                findColumn(selectItem, table, queryBlock, targetCol);
                                return;
                            }
                        }
                    }
                    if (columnObj instanceof SQLCaseExpr) {
                        SQLCaseExpr caseExpr = (SQLCaseExpr) columnObj;
                        //处理case when中的每个when then
                        columnFilter.caseFilter(caseExpr, usedFor, (sqlObj, uf) -> findColumn(sqlObj, schema, scope, targetCol, uf));
                    } else if (columnObj instanceof SQLCaseExpr.Item) {
                        SQLCaseExpr.Item caseExprItem = (SQLCaseExpr.Item) columnObj;
                        //分别处理when和then
                        columnFilter.caseItemFilter(caseExprItem, usedFor, (sqlObj, uf) -> findColumn(sqlObj, schema, scope, targetCol, uf));
                    } else if (columnObj instanceof SQLMethodInvokeExpr) {
                        SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) columnObj;
                        //处理普通函数
                        columnFilter.methodFilter(methodInvokeExpr, usedFor, (sqlObj, uf) -> findColumn(sqlObj, schema, scope, targetCol, uf));
                    } else if (!columnObj.getClass().equals(SQLIdentifierExpr.class)) {
                        //寻找内部字段
                        ClassUtils.loopField(columnObj.getClass(), columnObj, o -> {
                            if (o instanceof List) {
                                for (Object x : (List) o) {
                                    if (x instanceof SQLObject) {
                                        findColumn((SQLObject) x, schema, scope, targetCol);
                                    }
                                }
                            } else if (o instanceof SQLObject) {
                                findColumn((SQLObject) o, schema, scope, targetCol);
                            }
                        });
                    }
                }
            }
        }
    }

    private void findColumn(SQLObject columnObj, Object result, SQLSelectQueryBlock scope, Column targetCol, String usedFor) {
        findColumn(columnObj, result, scope, targetCol, new HashSet<>(Collections.singleton(usedFor)));
    }

    private void findColumn(SQLObject columnObj, Object result, SQLSelectQueryBlock scope, Column targetCol) {
        findColumn(columnObj, result, scope, targetCol, DATA);
    }

    private void findTable(DB db, SQLTableSource tableSource, Schema schema, SQLSelectQueryBlock scope) {
        if (tableSource instanceof SQLExprTableSource) {
            SQLExprTableSource exprTableSource = (SQLExprTableSource) tableSource;
            SQLExpr tableSourceExpr = exprTableSource.getExpr();
            //处理简单select from（支持不用别名）
            if (exprTableSource.getParent() instanceof SQLSelectQueryBlock) {
                SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) exprTableSource.getParent();
                if (queryBlock.getFrom() instanceof SQLExprTableSource) {   //from部分是简单表
                    List<Table> tables = schema.getTables();
                    for (int i = 0; i < tables.size(); i++) {
                        Table table = tables.get(i);
                        String exprName;
                        if (tableSourceExpr instanceof SQLPropertyExpr) {
                            exprName = ((SQLPropertyExpr) tableSourceExpr).getName();
                        } else {
                            exprName = ((SQLIdentifierExpr) tableSourceExpr).getName();
                        }
                        if (exprName.equalsIgnoreCase(table.getName())) {
                            Schema newSchema = new Schema();
                            if (tableSourceExpr instanceof SQLPropertyExpr) {
                                newSchema.setName(((SQLIdentifierExpr) ((SQLPropertyExpr) tableSourceExpr).getOwner()).getName());
                            }
                            //与现有schema对比并合并成最终schema
                            newSchema = db.absorbSchema(newSchema);
                            //与现有表比对并合成最终表
                            table = newSchema.absorbTable(table);
                            //让表记录作用域和对应的表别名
                            table.putAlias(queryBlock, table.getName());
                            //确保schema中的这张表时最终合成的
                            tables.set(i, table);
                            break;
                        }
                    }
                    return;
                }
            }
            if (!StringUtils.isEmpty(exprTableSource.getAlias())) { //非简单select from语句，但是有别名
                List<Table> tables = schema.getTables();
                for (int i = 0; i < tables.size(); i++) {
                    Table table = tables.get(i);
                    if (exprTableSource.getAlias().equalsIgnoreCase(table.getName())) {
                        if (tableSourceExpr instanceof SQLPropertyExpr) {
                            table.setName(((SQLPropertyExpr) tableSourceExpr).getName());
                        } else {
                            table.setName(((SQLIdentifierExpr) tableSourceExpr).getName());
                        }
                        table.isTemp(false);
                        Schema newSchema = new Schema();
                        if (tableSourceExpr instanceof SQLPropertyExpr) {
                            newSchema.setName(((SQLIdentifierExpr) ((SQLPropertyExpr) tableSourceExpr).getOwner()).getName());
                        }
                        //与现有schema对比并合并成最终schema
                        newSchema = db.absorbSchema(newSchema);
                        //与现有表比对并合成最终表
                        table = newSchema.absorbTable(table);
                        //让表记录作用域和对应的表别名
                        table.putAlias(scope, exprTableSource.getAlias());
                        //确保schema中的这张表时最终合成的
                        tables.set(i, table);
                        break;
                    }
                }
            }
        } else if (tableSource instanceof SQLSubqueryTableSource) { //属于子查询
            SQLSelectQuery query = ((SQLSubqueryTableSource) tableSource).getSelect().getQuery();
            for (Table table : schema.getTables()) {
                String alias = tableSource.getAlias();
                if (StringUtils.isEmpty(alias)) {
                    //将整个子查询部分md5加密作为临时表
                    alias = Md5Utils.encrypt(tableSource.toString().getBytes());
                }
                if (alias.equalsIgnoreCase(table.getName())) {
                    if (query instanceof SQLSelectQueryBlock) {
                        selectForSelectAnalysis(db, (SQLSelectQueryBlock) query, table);
                    } else if (query instanceof SQLUnionQuery) {
                        unionSelectForSelectAnalysis(db, query, table, new ArrayList<>());
                    }
                    break;
                }
            }
        } else if (tableSource instanceof SQLUnionQueryTableSource) {   //属于union子查询
            SQLUnionQuery union = ((SQLUnionQueryTableSource) tableSource).getUnion();
            for (Table table : schema.getTables()) {
                String alias = tableSource.getAlias();
                if (StringUtils.isEmpty(alias)) {
                    //将整个子查询部分md5加密作为临时表
                    alias = Md5Utils.encrypt(tableSource.toString().getBytes());
                }
                if (alias.equalsIgnoreCase(table.getName())) {
                    unionSelectForSelectAnalysis(db, union, table, new ArrayList<>());
                    break;
                }
            }
        } else if (tableSource instanceof SQLJoinTableSource) {
            //继续查找表
            ClassUtils.loopField(SQLJoinTableSource.class, tableSource, o -> {
                if (o instanceof SQLTableSource) {
                    findTable(db, (SQLTableSource) o, schema, scope);
                }
            });

        }
    }

}
